package cn.com.callback

import groovy.json.*;
import groovy.swing.impl.DefaultAction;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.RequestDispatcher;
import cn.com.elite.EliteBean;
import java.security.MessageDigest;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.wisdge.dataservice.Result;
import java.util.Map;
import java.util.Date;
import java.util.HashMap;
import cn.com.elite.Util;
import cn.com.kh.UcisXML;
import java.net.URLEncoder;

import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;

/**
 *
 * @Title: BigdateOnlineToday
 * @Description:大数据定时更新每日线上数据
 * @author: Yuchieh 
 * @date 2021-07-13
 *
 */


public class BigdateOnlineToday implements ServiceBackInvoke{
	private static final Log log=LogFactory.getLog(BigdateOnlineToday.class);
	public String doCallBack(EliteBean eliteBean,HttpServletRequest request,HttpServletResponse response){
				
		response.setCharacterEncoding("UTF-8");
		JSONObject resulterror = new JSONObject();
				

		try{
			//将数据写进his表
			def todaysql_bak = "INSERT INTO bigdateOnline_his SELECT *,GETDATE() FROM bigdateOnline";
			//eliteBean.update(todaysql_bak, "dataSource");
			//清空表数据
			def todaysql_del = "TRUNCATE TABLE bigdateOnline";
			eliteBean.update(todaysql_del, "dataSource");
			//写入数据
			def todaysql_ins1 = "INSERT INTO bigdateOnline SELECT  T1.Area_desc '区域',[热线电话],[电话留言],[水星网站],[数字化城管网站],[12345政府公开电话],[传真],[函件],[来人],[大众媒体],[自查及日常安排],[电子邮件],[水务局网站],[网上营业厅],[微信公众平台],[线上平台-自助终端],[线上平台-微信公众号],[线上平台-网厅],[线上平台-i深圳],isnull([已处理工单],0) 已处理工单,[工单短信],[通讯录短信],[人工短信],[人工批次发送],[智慧停水发送] FROM ( SELECT ga.Area_desc, SUM(CASE WHEN c.Code_ID = '1' THEN 1 ELSE 0 END)'热线电话', SUM(CASE WHEN c.Code_ID = '2' THEN 1 ELSE 0 END)'电话留言', SUM(CASE WHEN c.Code_ID = '3' THEN 1 ELSE 0 END)'水星网站', SUM(CASE WHEN c.Code_ID = '4' THEN 1 ELSE 0 END)'数字化城管网站', SUM(CASE WHEN c.Code_ID = '5' THEN 1 ELSE 0 END)'12345政府公开电话', SUM(CASE WHEN c.Code_ID = '6' THEN 1 ELSE 0 END)'传真', SUM(CASE WHEN c.Code_ID = '7' THEN 1 ELSE 0 END)'函件', SUM(CASE WHEN c.Code_ID = '8' THEN 1 ELSE 0 END)'来人', SUM(CASE WHEN c.Code_ID = '9' THEN 1 ELSE 0 END)'大众媒体', SUM(CASE WHEN c.Code_ID = '10' THEN 1 ELSE 0 END)'自查及日常安排', SUM(CASE WHEN c.Code_ID = '11' THEN 1 ELSE 0 END)'电子邮件', SUM(CASE WHEN c.Code_ID = '12' THEN 1 ELSE 0 END)'水务局网站', SUM(CASE WHEN c.Code_ID = '13' THEN 1 ELSE 0 END)'网上营业厅', SUM(CASE WHEN c.Code_ID = '14' THEN 1 ELSE 0 END)'微信公众平台',SUM(CASE WHEN c.Code_ID = '15' THEN 1 ELSE 0 END)'线上平台-自助终端', SUM(CASE WHEN c.Code_ID = '16' THEN 1 ELSE 0 END)'线上平台-微信公众号', SUM(CASE WHEN c.Code_ID = '17' THEN 1 ELSE 0 END)'线上平台-网厅', SUM(CASE WHEN c.Code_ID = '18' THEN 1 ELSE 0 END)'线上平台-i深圳' from TABCswgdmerge t  LEFT JOIN C_CodeTables c on c.code_id = t.operationsource AND use_type='业务来源' LEFT JOIN Group_Area ga on ga.area_id = t.receiver where CONVERT(VARCHAR(10),GETDATE(),120) = CONVERT(VARCHAR(10),registerdate,120) GROUP BY ga.Area_desc ) T1 LEFT JOIN( SELECT Area_desc,COUNT(Area_desc) 已处理工单 FROM  objective o LEFT JOIN TABCswgdmerge t1 on o.objective_guid=t1.relation_id LEFT JOIN Group_Area ga on ga.area_id = t1.RECEIVER where 1=1 AND CONVERT(VARCHAR(10),GETDATE(),120) = CONVERT(VARCHAR(10),registerdate,120) AND map_id = 'ZHS14C' AND o.objectivestatus = 'CLOSE' GROUP BY Area_desc )T2 ON T1.Area_desc = T2.Area_desc LEFT JOIN ( SELECT Area_desc, SUM(CASE WHEN DocumentOut.STRINGFIELD1 = '工单' THEN 1 ELSE 0 END)'工单短信', SUM(CASE WHEN DocumentOut.STRINGFIELD1 = '通讯录短信' THEN 1 ELSE 0 END)'通讯录短信', SUM(CASE WHEN DocumentOut.STRINGFIELD1 = '人工短信' THEN 1 ELSE 0 END)'人工短信', SUM(CASE WHEN DocumentOut.STRINGFIELD1 = '人工批次发送' THEN 1 ELSE 0 END)'人工批次发送', SUM(CASE WHEN DocumentOut.STRINGFIELD1 = '智慧停水发送' THEN 1 ELSE 0 END)'智慧停水发送' FROM DocumentOut  LEFT JOIN RolegroupArea on RolegroupArea.Group_id = DocumentOut.HANDLEGROUP_ID LEFT JOIN Group_Area on Group_Area.Area_guid = RolegroupArea.Area_guid WHERE CONVERT(VARCHAR(10),GETDATE(),120) = CONVERT(VARCHAR(10),ExpectedStartDate,120) GROUP BY Area_desc )T3 ON T1.Area_desc = T3.Area_desc ORDER BY CHARINDEX(T1.Area_desc,'集团宝安龙岗光明') ";
			eliteBean.update(todaysql_ins1, "dataSource");

			
			resulterror.put("code", "1");
			resulterror.put("msg", "每日线上数据写入完成");
			return  resulterror;
			
		}catch(Exception e){
			resulterror.put("code", "-1");
			resulterror.put("msg", "接口异常！"+e);
			return  resulterror;
		}
	}
}